# -*- coding: utf-8 -*-
"""
@Time ： 2023/10/13 14:06
@Auth ： dorabmon
@File ：write.py
@IDE ：PyCharm
"""
import xlrd
import pymysql

# 建立数据库连接
def connect_to_mysql():
    try:
        conn = pymysql.connect(
            host='localhost',
            user='root',
            password='123456',
            database='student'
        )
        return conn
    except pymysql.Error as err:
        print(f"Error: {err}")
        return None

# 关闭数据库连接
def close_connection(conn):
    conn.close()

# 将Excel数据写入MySQL
def write_excel_to_mysql(conn, sheet, table_name):
    cursor = conn.cursor()

    for row in range(1, sheet.nrows):
        values = sheet.row_values(row)
        placeholders = ', '.join(['%s'] * len(values))
        columns = ', '.join(sheet.row_values(0))
        sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        cursor.execute(sql, values)

    conn.commit()
    cursor.close()

# 打开Excel文件
workbook = xlrd.open_workbook('baidu-员工的人员信息.xls')
sheet = workbook.sheet_by_index(0)

# 建立数据库连接
connection = connect_to_mysql()

if connection:
    # 将Excel数据写入MySQL数据库
    write_excel_to_mysql(connection, sheet, 'baidu')

    # 关闭数据库连接
    close_connection(connection)

